package com.augurit.gzcgwsp.ssgd.qx.service.impl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import jxl.Sheet;
import jxl.Workbook;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;

import com.augurit.common.syscode.service.ISysCodeService;
import com.augurit.common.syscode.web.form.SysCodeForm;
import com.augurit.common.system.web.auth.form.LoginUserForm;
import com.augurit.common.util.HqlUtils;
import com.augurit.common.util.PageUtils;
import com.augurit.common.util.lang.StringUtils;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.qyzz.dao.YwRqQyzzInfoDao;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzInfo;
import com.augurit.gzcgwsp.rqqyjyxk.dao.YwRqQyjyxkDao;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxk;
import com.augurit.gzcgwsp.sendNotice.service.ICreateNoticeCommonPartService;
import com.augurit.gzcgwsp.sendNotice.service.IExchangeStateService;
import com.augurit.gzcgwsp.sendNotice.service.IYwMtbjlshInfoService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceForWbService;
import com.augurit.gzcgwsp.sendNotice.service.IYwWebserviceInfoService;
import com.augurit.gzcgwsp.ssgd.convert.YwRqSsgdSqbConvertor;
import com.augurit.gzcgwsp.ssgd.dao.YwRqSsgdSqbDao;
import com.augurit.gzcgwsp.ssgd.entity.YwRqSsgdSqb;
import com.augurit.gzcgwsp.ssgd.qx.service.IYwRqSsgdSqbQxService;
import com.augurit.gzcgwsp.ssgd.web.form.YwRqSsgdSqbForm;
import com.augurit.gzcgwsp.wfex.common.service.IWfServiceEX;
import com.augurit.gzcgwsp.ywcmn.entity.YwComSjcllist;
import com.augurit.gzcgwsp.ywcmn.service.IYwComQyxxService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjclService;
import com.augurit.gzcgwsp.ywcmn.service.IYwComSjcllistService;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComQyxxForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjclForm;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComSjcllistForm;
import com.augurit.wf.bi.web.form.WfBusInstanceTaskForm;
import com.augurit.wf.common.service.impl.WfBusClientServiceImpl;

@Service
@Transactional
public class YwRqSsgdSqbQxServiceImpl extends WfBusClientServiceImpl<YwRqSsgdSqbForm> implements IYwRqSsgdSqbQxService{

	@Autowired
	private YwRqSsgdSqbDao ywRqSsgdSqbDao;
    @Autowired
    private IYwComQyxxService ywComQyxxService;
    @Autowired
    private IYwComSjclService sjclService;
    @Autowired
	private ICreateNoticeCommonPartService createNoticeCommonPartService;
    @Autowired
	private IYwComSjcllistService ywComSjcllistService;
    @Autowired
    private IYwComSjclService ywComSjclService;
    @Autowired
    private YwRqQyzzInfoDao ywRqQyzzInfoDao;
	@Autowired
	private YwRqQyjyxkDao ywRqQyjyxkDao;
    @Autowired
    private ISysCodeService syscodeService;
    @Autowired
    private IYwMtbjlshInfoService ywMtbjlshInfoService;
    @Autowired
	private IYwWebserviceInfoService ywWebserviceInfoService;
    @Autowired
    private IYwWebserviceForWbService ywWebserviceForWbService;
    @Autowired
    private IExchangeStateService exchangeStateService;
    @Autowired
    private IWfServiceEX wfServiceEX;
    
	@Transactional(readOnly = true)
	public YwRqSsgdSqbForm get(Long id) {
		YwRqSsgdSqb entity = ywRqSsgdSqbDao.get(id);
		YwRqSsgdSqbForm form = YwRqSsgdSqbConvertor.convertVoToForm(entity);
		YwComQyxxForm qyxxform = ywComQyxxService.get(form.getQyid());
		YwRqSsgdSqbConvertor.convertQyxxFormToForm(qyxxform, form);
		
		YwComSjcllistForm sjclListForm=new YwComSjcllistForm();
		sjclListForm.setAjid(id);
		sjclListForm.setTplcode("rqssgdqx");
        List<YwComSjcllistForm> sjclList = ywComSjcllistService.getList(sjclListForm);
        YwComSjclForm fkForm = new YwComSjclForm();
        fkForm.setFkid(form.getQyid());
        fkForm.setAjid(id);
        fkForm.setTplcode("rqssgdqx");
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        for (YwComSjcllistForm listForm : sjclList) {
            boolean hasExists = false;
            for (YwComSjclForm tplForm : sjclTpl) {
                if (tplForm.getTplid() != null && listForm.getId().longValue() == tplForm.getTplid().longValue()) {
                    if (StringUtils.isNotBlank(tplForm.getFilepath())) {
                        listForm.setState("1");
                        listForm.setHasfile("1");
                        listForm.setFilename(tplForm.getFilename());
                        listForm.setFilepath(tplForm.getFilepath());
                        listForm.setFiletype(tplForm.getFiletype());
                    } else {
                        listForm.setState("0");
                        listForm.setHasfile("0");
                    }
                    if (tplForm.getYjs() != null) {
                        listForm.setYjs(Long.valueOf(tplForm.getYjs()));
                    }
                    if (tplForm.getFyjs() != null) {
                        listForm.setFyjs(Long.valueOf(tplForm.getFyjs()));
                    }
                    listForm.setClid(tplForm.getId());
                    if ("1".equals(listForm.getSfdz())) {
                        listForm.setDisabled("1");
                    } else {
                        listForm.setDisabled("0");
                        if (tplForm.getSfdz() == null) {
                            listForm.setSfdz("0");
                        } else {
                            listForm.setSfdz(tplForm.getSfdz());
                        }
                    }
                    hasExists = true;
                }
            }
            if (!hasExists) {
                listForm.setState("0");
                listForm.setHasfile("0");
                if ("1".equals(listForm.getSfdz())) {
                    listForm.setDisabled("1");
                } else {
                    listForm.setDisabled("0");
                }
            }
        }
        if(sjclList.size()>0)
		{
        	YwComSjcllistForm sjclForm01=sjclList.get(0);
			form.setTitleRow101(sjclForm01.getTitle());
			form.setTitleRow102(sjclForm01.getTitle());
			form.setFyjsRow101(sjclForm01.getFyjs());
			form.setFyjsRow102(sjclForm01.getFyjs());
			form.setFsRow101(sjclForm01.getYjs()+sjclForm01.getFyjs());
			form.setFsRow102(sjclForm01.getYjs()+sjclForm01.getFyjs());
			if("0".equals(sjclForm01.getSfdz()))
			{
				form.setSfdzRow101("");
				form.setSfdzRow102("");
			}else{
				form.setSfdzRow101("√");
				form.setSfdzRow102("√");
			}
			
			YwComSjcllistForm sjclForm02=sjclList.get(1);
			form.setTitleRow201(sjclForm02.getTitle());
			form.setTitleRow202(sjclForm02.getTitle());
			form.setFyjsRow201(sjclForm02.getFyjs());
			form.setFyjsRow202(sjclForm02.getFyjs());
			form.setFsRow201(sjclForm02.getYjs()+sjclForm02.getFyjs());
			form.setFsRow202(sjclForm02.getYjs()+sjclForm02.getFyjs());
			if("0".equals(sjclForm02.getSfdz()))
			{
				form.setSfdzRow201("");
				form.setSfdzRow202("");
			}else{
				form.setSfdzRow201("√");
				form.setSfdzRow202("√");
			}
			
			YwComSjcllistForm sjclForm03=sjclList.get(2);
			form.setTitleRow301(sjclForm03.getTitle());
			form.setTitleRow302(sjclForm03.getTitle());
			form.setFyjsRow301(sjclForm03.getFyjs());
			form.setFyjsRow302(sjclForm03.getFyjs());
			form.setFsRow301(sjclForm03.getYjs()+sjclForm03.getFyjs());
			form.setFsRow302(sjclForm03.getYjs()+sjclForm03.getFyjs());
			if("0".equals(sjclForm03.getSfdz()))
			{
				form.setSfdzRow301("");
				form.setSfdzRow302("");
			}else{
				form.setSfdzRow301("√");
				form.setSfdzRow302("√");
			}
			
			YwComSjcllistForm sjclForm04=sjclList.get(3);
			form.setTitleRow401(sjclForm04.getTitle());
			form.setTitleRow402(sjclForm04.getTitle());
			form.setFyjsRow401(sjclForm04.getFyjs());
			form.setFyjsRow402(sjclForm04.getFyjs());
			form.setFsRow401(sjclForm04.getYjs()+sjclForm04.getFyjs());
			form.setFsRow402(sjclForm04.getYjs()+sjclForm04.getFyjs());
			if("0".equals(sjclForm04.getSfdz()))
			{
				form.setSfdzRow401("");
				form.setSfdzRow402("");
			}else{
				form.setSfdzRow401("√");
				form.setSfdzRow402("√");
			}
			
			
			YwComSjcllistForm sjclForm05=sjclList.get(4);
			form.setTitleRow501(sjclForm05.getTitle());
			form.setTitleRow502(sjclForm05.getTitle());
			form.setFyjsRow501(sjclForm05.getFyjs());
			form.setFyjsRow502(sjclForm05.getFyjs());
			form.setFsRow501(sjclForm05.getYjs()+sjclForm05.getFyjs());
			form.setFsRow502(sjclForm05.getYjs()+sjclForm05.getFyjs());
			if("0".equals(sjclForm05.getSfdz()))
			{
				form.setSfdzRow501("");
				form.setSfdzRow502("");
			}else{
				form.setSfdzRow501("√");
				form.setSfdzRow502("√");
			}
			
			
			YwComSjcllistForm sjclForm06=sjclList.get(5);
			form.setTitleRow601(sjclForm06.getTitle());
			form.setTitleRow602(sjclForm06.getTitle());
			form.setFyjsRow601(sjclForm06.getFyjs());
			form.setFyjsRow602(sjclForm06.getFyjs());
			form.setFsRow601(sjclForm06.getYjs()+sjclForm06.getFyjs());
			form.setFsRow602(sjclForm06.getYjs()+sjclForm06.getFyjs());
			if("0".equals(sjclForm06.getSfdz()))
			{
				form.setSfdzRow601("");
				form.setSfdzRow602("");
			}else{
				form.setSfdzRow601("√");
				form.setSfdzRow602("√");
			}
			
			YwComSjcllistForm sjclForm07=sjclList.get(6);
			form.setTitleRow701(sjclForm07.getTitle());
			form.setTitleRow702(sjclForm07.getTitle());
			form.setFyjsRow701(sjclForm07.getFyjs());
			form.setFyjsRow702(sjclForm07.getFyjs());
			form.setFsRow701(sjclForm07.getYjs()+sjclForm07.getFyjs());
			form.setFsRow702(sjclForm07.getYjs()+sjclForm07.getFyjs());
			form.setSerialNo2(form.getSerialNo());
			form.setPassword2(form.getPassword());
			if("0".equals(sjclForm07.getSfdz()))
			{
				form.setSfdzRow701("");
				form.setSfdzRow702("");
			}else{
				form.setSfdzRow701("√");
				form.setSfdzRow702("√");
			}
		}
        
		
        form.setLxr2(form.getYwlxr());
        if(form.getSqrq2() != null)
        	form.setSqrq(form.getSqrq2());
		return form;
	}
	
	//通过案件号获得实体类
    public List<YwRqSsgdSqb> getByAcceptNo(String acceptNo) {
    	String sql = "from YwRqSsgdSqb t where t.acceptNo='" + acceptNo + "'";
        List<YwRqSsgdSqb> entityList = ywRqSsgdSqbDao.find(sql);
        return entityList;
    }
	
    public void changeYslState(Long id,Long state,String yslThyy) {
    	StringBuffer sql = new StringBuffer();
    	sql.append("update YwRqSsgdSqb t set t.ysl="+state);
    	if(StringUtils.isNotBlank(yslThyy)){
    		sql.append(" ,t.yslThyy='"+yslThyy+"'");
    	}
    	sql.append(" where t.id="+id);
    	ywRqSsgdSqbDao.getSession().createQuery(sql.toString()).executeUpdate();
    	System.out.println("设施改动收件");
    }
    
    //是否延期办理
    public void changeIsycbl(Long id, String state) {
//    	YwRqSsgdSqbForm form = this.get(id);
//    	form.setIsycbl(state);
//    	this.save(form);
    	
    	YwRqSsgdSqb entity = ywRqSsgdSqbDao.get(id);
    	entity.setIsycbl(state);
    	ywRqSsgdSqbDao.save(entity);
		
	}

    //保存结案时间
	public void endSave(Long id) {
//		YwRqSsgdSqbForm form = this.get(id);
//		form.setJarq(new Date());
//		this.save(form);
		
		YwRqSsgdSqb entity = ywRqSsgdSqbDao.get(id);
    	entity.setJarq(new Date());
    	ywRqSsgdSqbDao.save(entity);
	}
    
	public void delete(Long... ids) {
		ywRqSsgdSqbDao.delete(ids);
	}

	public void save(YwRqSsgdSqbForm... forms) {
		if(forms != null)
			for(YwRqSsgdSqbForm form : forms)
				this.save(form);
	}

	public void save(YwRqSsgdSqbForm form){
		if (form == null) return;
		
		YwRqSsgdSqb entity = null;
		if(form != null && form.getId() != null){
			entity = ywRqSsgdSqbDao.get(form.getId());
			if (StringUtils.isBlank(entity.getAcceptNo()) || form.getAcceptNo().indexOf("XXXX") > 0) form.setAcceptNo(null);
		}else{
			entity = new YwRqSsgdSqb();
            form.setAcceptNo(null);
		}
        
		//保存企业信息
        YwComQyxxForm qyxxForm = new YwComQyxxForm();
        if(form.getQyid() == null){
        	if(StringUtils.isNotBlank(form.getSqdw())){
            	YwComQyxxForm ywComQyxxForm = ywComQyxxService.getFormBySqdw(form.getSqdw(), "rqssgdqx");
            	if(ywComQyxxForm != null){
            		qyxxForm = ywComQyxxForm;
            	}
            }
        }
        YwRqSsgdSqbConvertor.convertFormToQyxxForm(form, qyxxForm);
        qyxxForm.setTemplateCode("rqssgdqx");
        ywComQyxxService.update(qyxxForm);        
        form.setQyid(qyxxForm.getId());
        
        boolean isNewForm = false;
        if (StringUtils.isBlank(form.getAcceptNo())) {
        	//生成政务中心统一查询编号 开始
			Map<String,String> serialNoAndPasswordMap = null;
        	if("Y".equals(form.getSendNotice())){//网办，1
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("1");
        	}else{//大厅，0
        		serialNoAndPasswordMap = ywMtbjlshInfoService.getSerialNoAndPassword("0");
        	}
        	String serialNo = serialNoAndPasswordMap.get("serialNo");
        	String password = serialNoAndPasswordMap.get("password");
			form.setSerialNo(serialNo);
			form.setPassword(password);
			form.setAcceptNo(serialNo);
    		//生成政务中心统一查询编号 结束
            isNewForm = true;
        }
        YwRqSsgdSqbConvertor.convertFormToVo(form, entity);
        ywRqSsgdSqbDao.save(entity);
		form.setId(entity.getId());
		if (isNewForm) {
		    sjclService.updateByNewForm(form.getId());
		}
	}

	/**
	 */
	@Transactional(readOnly = true)
	public Page<YwRqSsgdSqbForm> search(Page<YwRqSsgdSqbForm> page, YwRqSsgdSqbForm form) {
		Page pg = PageUtils.newPageInstance(page);
		
		StringBuffer hql = new StringBuffer("from YwRqSsgdSqb ps where 1=1");
		List values = new ArrayList();
		
		if(form != null){
			
		}

		hql.append(HqlUtils.buildOrderBy(page, "ps"));
		

		ywRqSsgdSqbDao.findPage(pg, hql.toString(), values);
		

		List<YwRqSsgdSqbForm> list = YwRqSsgdSqbConvertor.convertVoListToFormList(pg.getResult());
		PageUtils.copy(pg, list, page);
		return page;
	}
	
	/**

	 */
	@Transactional(readOnly = true)
	public Page<YwRqSsgdSqbForm> search(Page<YwRqSsgdSqbForm> page, List<PropertyFilter> filters) {

		Page<YwRqSsgdSqb> pg = PageUtils.newPageInstance(page);

		ywRqSsgdSqbDao.findPage(pg, filters);
		

		List<YwRqSsgdSqbForm> list = YwRqSsgdSqbConvertor.convertVoListToFormList(pg.getResult());
		PageUtils.copy(pg, list, page);
		return page;
	}

	/**
	 * 通过ID号,改变案件公示状态
	 * @param acceptNo 受理号
	 * @return Page<YwRqSsgdSqb>
	 */
	public boolean changeIsAjgsById(String state,Long id,Date date,Date endDate){
		boolean flag = true;
		YwRqSsgdSqb entity = ywRqSsgdSqbDao.get(id);
		try{
			if("1".equals(state)){//公示				
				YwRqSsgdSqbForm ywRqSsgdSqbForm = this.get(id);
				
				entity.setIsAjgs("1");
				entity.setGskssj(date);//设置案件开始公示时间
				ywRqSsgdSqbForm.setGskssj(date);
				if(endDate != null){
					entity.setGsjssj(endDate);
					ywRqSsgdSqbForm.setGsjssj(endDate);
				}
				createNoticeCommonPartService.createHtml(ywRqSsgdSqbForm);//产生公示html
			}else{//结束公示
				entity.setIsAjgs("0");
				entity.setGsjssj(date);//设置案件结束公示时间
			}    	
			System.out.println("ApprovalNo1 = "+entity.getApprovalNo());
			
			ywRqSsgdSqbDao.save(entity);
		}catch(Exception e){
			flag = false;
			e.printStackTrace();			
		}		
		return flag;
	}
	
	/**
	 * 保存实体
	 * @param entity
	 */
	public void save(YwRqSsgdSqb entity){
		ywRqSsgdSqbDao.save(entity);
	}
	
	/**
	 * 通过ID号,改变窗口是否可打印受理通知书状态(1为是；0和空为否)
	 * @param acceptNo 受理号
	 * @return Page<YwRqSsgdSqb>
	 */
	public void changeSendAcceptNotice(Long id){
		YwRqSsgdSqb entity = ywRqSsgdSqbDao.get(id);
		entity.setSendAcceptNotice("1");
    	ywRqSsgdSqbDao.save(entity);
	}
	
	/**
	 * 通过ID号,改变窗口是否可打印延期办理结果通知书状态(1为是；0和空为否)
	 * @param id 
	 * @return 
	 */
	public void changeSendYqNotice(Long id){
		YwRqSsgdSqb entity = ywRqSsgdSqbDao.get(id);
		entity.setSendYqNotice("1");
    	ywRqSsgdSqbDao.save(entity);
	}
	
	/**
     * 通过id改变SendAcceptNotice或者SendNotice状态,点击受理通知书和延期通知书页面的[已打印]时，调用
     * @return
     * @throws Exception
     */
	public void changeSendAcceptNoticeOrSendNoticeById(Long id,String sendYqNotice,String sendAcceptNotice,String templateCode){
		
		if("rqssgd".equals(templateCode)){
			YwRqSsgdSqb entity = ywRqSsgdSqbDao.get(id);	
			if("1".equals(sendAcceptNotice)){
				entity.setSendAcceptNotice("0");
			}else if("1".equals(sendYqNotice)){
				entity.setSendYqNotice("0");	
			}	
			ywRqSsgdSqbDao.save(entity);
		}else if("rqrsqjazwxqyzz".equals(templateCode)){
			YwRqQyzzInfo entity = ywRqQyzzInfoDao.get(id);	
			if("1".equals(sendAcceptNotice)){
				entity.setSendAcceptNotice("0");
			}else if("1".equals(sendYqNotice)){
				entity.setSendYqNotice("0");	
			}	
			ywRqQyzzInfoDao.save(entity);
		}else if("rqjyxkz_pzqy".equals(templateCode) || "rqjyxkz_gdqy".equals(templateCode)){
			YwRqQyjyxk entity = ywRqQyjyxkDao.get(id);	
			if("1".equals(sendAcceptNotice)){
				entity.setSendAcceptNotice("0");
			}else if("1".equals(sendYqNotice)){
				entity.setSendYqNotice("0");	
			}	
			ywRqQyjyxkDao.save(entity);
		}    	
	}
	
	/**
	 * 根据ID获得对象实体
	 * @param id
	 * @return
	 */  
	public YwRqSsgdSqb getEntity(Long id){
		return ywRqSsgdSqbDao.get(id);
	}
	
	/**
     * 保存对象实体
     * @param ywRqSsgdSqb
     */     
    public void saveEntity(YwRqSsgdSqb ywRqSsgdSqb){
    	ywRqSsgdSqbDao.save(ywRqSsgdSqb);
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.ssgd.service.IYwRqSsgdSqbService#checkData(java.lang.String, java.lang.String, java.lang.Long, java.lang.Long)
     */
	public boolean checkData(String templateCode, String filename, Long ajid,
			Long qyid) {
			 Long tempId=getTempId(templateCode, filename);
		        //查询上传的材料
		        //List<YwComSjcl>  sjcls=this.ywComSjclService.getFormByAjId(Long.valueOf(ajid),tempId);
		        List<YwComSjclForm> sjclTpl = this.getFiles(qyid, ajid, templateCode);
		        if(!hasFile(tempId,sjclTpl)){
		            return false;
		        }else{
		            return true;
		        }
		}
		
    /**
     * 返回模版id
     * @param templateCode 模版材料名称
     * @return
     */
    private Long getTempId(String templateCode,String filename){
        //查询材料模版
        List<YwComSjcllist> sjcllists = ywComSjcllistService.getYwComSjcls(templateCode);
        Long tempId=0l;
        for(YwComSjcllist entity:sjcllists){
            String title=entity.getTitle();
            if(title.indexOf(filename)>=0){
                tempId=entity.getId();
                break;
            }
        }
        return tempId;
    }
    /**
     * 是否存在文件
     * @return
     */
    private boolean hasFile(Long tempId, List<YwComSjclForm> list){
        String filenames="";
        if(list!=null && list.size()>0){
            for(YwComSjclForm entity:list){
                    if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                        String filename=entity.getFilename();
                        if(StringUtils.isNotBlank(filename)){
                            filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                            if("xls".equals(filename)){
                                filenames=filename;
                                break;
                            }
                        }
                    }
               }
            }
           if(StringUtils.isNotBlank(filenames))  return true;
        
        return false;
    }
    /**
     * 获取所有的收件材料
     * @param qyid
     * @param ajid
     * @param templateCode
     * @return
     */
    private List<YwComSjclForm> getFiles(Long qyid,Long ajid,String templateCode){
        YwComSjclForm fkForm = new YwComSjclForm();
        fkForm.setFkid(qyid);
        fkForm.setAjid(ajid);
        fkForm.setTplcode(templateCode);
        List<YwComSjclForm> sjclTpl = ywComSjclService.getList(fkForm);
        return sjclTpl;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.ssgd.service.IYwRqSsgdSqbService#importData(com.augurit.gzcgwsp.ssgd.web.form.YwRqSsgdSqbForm, java.util.Map)
     */
	public String importData(YwRqSsgdSqbForm form, Map map) {
		String result="";
		String contextpath=(String) map.get("contextPath");
		Long id=(Long) map.get("id");
        //查询上传的材料
        String filepath=this.getSqbFilePath(map);
        filepath=(contextpath+filepath).replace("/", File.separator);
        File file=new File(filepath);
        if(StringUtils.isNotBlank(filepath)){
        	 Workbook wb;
        	  try {
				wb = Workbook.getWorkbook(file);
			    if(isCorrectFile(wb,1)){
                  Sheet[] sheets=wb.getSheets();
                  result+=this.importInfoData(sheets[0],id);
			   }
	        } catch (Exception e) {
				  result+="收件材料没有符合条件的文件\n";
		    }
        }else{
            result+="收件材料没有符合条件的文件\n";
            
        }
       
		return result;
	}
	
	
	
	 /**
     * 功能:导入 处置服务行政许可申请书导入模板.xls 入库操作<br>
     * 参数:<br>
     * 返回:<br>
     * 作者:庄国坤<br>
     * 说明:<br>
     */
    public String importInfoData(Sheet sheet,Long id)  {
        String rt="";
        String hql=" from "+YwRqSsgdSqb.class.getName()+" where id="+id;
        
       List<YwRqSsgdSqb>  entitys=ywRqSsgdSqbDao.find(hql);
       
        int row=1;	
        //企业名称
        String companyName = ExcelUtil.readData(sheet,3,row);
        String sqdate=ExcelUtil.readData(sheet, 3, row);
        row++;
        String lxr=ExcelUtil.readData(sheet, 3, row);
        String tel=ExcelUtil.readData(sheet, 6, row);
        String mobile=ExcelUtil.readData(sheet, 10, row);
        row++;
        String ssgdname=ExcelUtil.readData(sheet, 3, row);//设施改动名称
        row++;
        String ssgdplace=ExcelUtil.readData(sheet,3, row);//地点
        String area=ExcelUtil.readData(sheet, 10, row);//区域
        row++;
        String ssgdcontent=ExcelUtil.readData(sheet, 3, row);//改动主要内容
        row++;
        String yxarea=ExcelUtil.readData(sheet, 3, row);//影响区域
        row++;
        String areauser=ExcelUtil.readData(sheet, 3, row);//影响的用户
        row++;
        String planestartdate=ExcelUtil.readData(sheet,4, row);//计划开始时间
        String planeenddate=ExcelUtil.readData(sheet, 9, row);//计划结束时间
        row++;
        String ssgdtype=ExcelUtil.readData(sheet, 4, row);//设施改动的类型
        //String ssgdtype2=ExcelUtil.readData(sheet, 7, row);//设施改动的类型
        //String ssgdtype3=ExcelUtil.readData(sheet, 9, row);//设施改动的类型
       
        String ssgdreason=ExcelUtil.readData(sheet, 2, row+2);//设施改动原因
       
        if(entitys.size()>0){
        	YwRqSsgdSqb  entity=entitys.get(0);
        	 YwComQyxxForm qyxxform = ywComQyxxService.get(entity.getQyid());
        	 qyxxform.setYwlxr(lxr);
        	 qyxxform.setYwlxrtel(tel);
        	 qyxxform.setYwlxphone(mobile);
        	 entity.setSsgdmc(ssgdname);
        	 entity.setAddress(ssgdplace);
        	
        	 entity.setGdzynr(ssgdcontent);
        	 entity.setKnsyxqy(yxarea);
        	 entity.setKnsyxyh(areauser);
        	 SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日");
        	 if(StringUtils.isNotBlank(area)){
        	        List<SysCodeForm> ywRqssgdQy = syscodeService.getItems("YW_RQSSGD_QY");
        	        for(SysCodeForm qy:ywRqssgdQy){
        	        	if(area.equals(qy.getItemName())){
        	        		 entity.setSsxzqy(qy.getItemCode());
        	        		break;
        	        	}
        	        }
        	 }
        	 try {
				 entity.setJhsssj(sdf.parse(planestartdate));
			} catch (Exception e) {
				 try {
					 String sdate=dayAddition(Integer.valueOf(planestartdate));  
					  entity.setJhsssj(sdf.parse(sdate));
				} catch (Exception e1) {
				  rt="计划开始时间格式为yyyy年mm月dd日\r\n";
				}
				 
			}
			 try {
				 entity.setJhwcsj(sdf.parse(planeenddate));
			} catch (Exception e) {
				 try {
					String edate=dayAddition(Integer.valueOf(planeenddate));  
					  entity.setJhwcsj(sdf.parse(edate));
				} catch (Exception e1) {
					rt+="计划结束时间格式为yyyy年mm月dd日\r\n";
				}
				
			}
        	 if("场站设施改动".equals(ssgdtype)){
        		 entity.setGdlx("1");
        	 }else if("管道设施改动".equals(ssgdtype)){
        		 entity.setGdlx("2");
        	 }else if("其他".equals(ssgdtype)){
        		 entity.setGdlx("3");
        	 }
        	 entity.setSsgdyy(ssgdreason);
        	 this.ywRqSsgdSqbDao.save(entity);
        	 this.ywComQyxxService.save(qyxxform);
       }
        
        return rt;
    }
    //日期相加天数   
    public static String dayAddition(int num) throws Exception{  
        SimpleDateFormat timeformat = new SimpleDateFormat("yyyy年MM月dd日");  
        java.util.Date date = timeformat.parse("1900年1月1日");  
        Calendar a = Calendar.getInstance();  
        a.setTime(date);  
        a.add(Calendar.DATE, (num-2));  
        return timeformat.format(a.getTime());  
    }  

    /**
     * 获取申请表文件
     * @param sjcls
     * @return
     */
    public String getSqbFilePath(Map map){
    	 String templateCode=(String) map.get("templateCode");
         Long ajid=(Long) map.get("id");
         String contextpath=(String) map.get("contextPath");
         String fileName=(String) map.get("filename");
         Long tempId=getTempId(templateCode,fileName);
        List<YwComSjclForm> sjcls = getFiles(null, Long.valueOf(ajid), templateCode);
        String filepath="";
        Workbook wb;
        try {
            for(YwComSjclForm entity:sjcls){
                if(String.valueOf(entity.getTplid()).equals(String.valueOf(tempId))){
                    filepath=entity.getFilepath();
                    if(StringUtils.isNotBlank(filepath)){
                        String[] paths=filepath.split(",");
                    for(String path:paths){
                            path=path.replace("/", File.separator);
                            File file=new File(contextpath+path);
                            String name=entity.getFilename();
                            if(StringUtils.isNotBlank(name)){
                                for(String filename:name.split(",")){
                                filename=filename.substring(filename.lastIndexOf(".")+1,filename.length());
                                if(!"xls".equals(filename)){
                                    continue;
                                }
                                if(!file.exists()){
                                    continue;
                                }else{
                                    wb = Workbook.getWorkbook(file);
                                    if(isCorrectFile(wb,1)){
                                        return path;
                                    }
                               }
                              }
                            }
                       
                        }
                    }
                }
            }
        } catch (Exception e) {
           e.printStackTrace();
        }
        return filepath;
    }
    
    
    /**
     * 是否是正确的文件格式
     * @param wb
     * @return
     */
    private boolean isCorrectFile(Workbook wb,int sheetsize){
        Sheet[] sheets=wb.getSheets();
        if(sheets.length>=sheetsize)
            return true;
        else
            return false;
    }
    
    
    public String wfBusYslTuih(YwRqSsgdSqbForm form,LoginUserForm loginUser) throws Exception {
    	String  loginUserName = loginUser.getUser().getUserName();
    	WfBusInstanceTaskForm taskForm = new WfBusInstanceTaskForm();
		taskForm.setHandleComments("资料不全，不予受理。");
		//把审批状态数据保存到ywWebserviceInfo ，交换给方欣
		ywWebserviceInfoService.saveYwWebserviceInfo(form.getSqrq(), form.getAcceptNo(), form.getCbyw(), form.getYwlxr(), form.getSjr(), form.getSqdw(), 
				form.getBusinessCode(), form.getApprovalNo(), loginUserName, loginUser.getOrg().getOrgName(), "ysl", new Date(), false, "预受理", taskForm);
//		System.out.println("form.getYslThyy() = "+java.net.URLDecoder.decode(form.getYslThyy(),"UTF-8"));
		this.changeYslState(form.getId(), 0L,taskForm.getHandleComments());
		
		//（网办）交换到市民网页
		Map map=exchangeStateService.buildMap(loginUser, null, form.getId().toString(), form.getTemplateCode(), false);
		String durationExpression = wfServiceEX.getDurationExpressionByTemplateCode(form.getTemplateCode());
		ywWebserviceForWbService.setValueAndSaveYwWebserviceForWb(loginUserName,form.getTemplateCode(),
				form.getId().toString(),durationExpression,"001",taskForm.getHandleComments(), map);
    	return null;
    }
}
